package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.MakeDTO;
import com.fact.utils.connection.ConnectionManager;


public class MakeService {
	
	public ArrayList<MakeDTO> getMakeList()
	{
		MakeDTO make=null;
		ArrayList<MakeDTO> makeList=new ArrayList<MakeDTO>();
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from MST_MAKES order by Make_Name";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				while (r.next())
				{
					make=new MakeDTO();
					make.setMakeId(r.getInt("MAKE_ID"));
					make.setMakeName(r.getString("MAKE_NAME"));
					makeList.add(make);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return makeList;
	}
	public boolean saveMake(MakeDTO	make)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_MAKES(MAKE_ID,MAKE_NAME) " +
				   " values(SQN_MAKE.nextval,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,make.getMakeName());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteMake(int makeId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_MAKES Where MAKE_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,makeId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editMake(MakeDTO make)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_MAKES  set MAKE_NAME=?  Where MAKE_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,make.getMakeName());
				stmt.setInt(2,make.getMakeId());
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public MakeDTO getMake(int makeId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * FROM MST_MAKES  Where MAKE_ID=?";
		PreparedStatement stmt = null;
		MakeDTO  make=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,makeId);
				r = stmt.executeQuery();
				if (r.next())
				{
					make=new MakeDTO();
					make.setMakeId(r.getInt("MAKE_ID"));
					make.setMakeName(r.getString("MAKE_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return make;
	}
	
	public MakeDTO getMake(String makeName)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * FROM MST_MAKES  Where lower(MAKE_NAME)=lower(?)";
		PreparedStatement stmt = null;
		MakeDTO  make=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,makeName);
				r = stmt.executeQuery();
				if (r.next())
				{
					make=new MakeDTO();
					make.setMakeId(r.getInt("MAKE_ID"));
					make.setMakeName(r.getString("MAKE_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return make;
	}

}